package edu.uwm.capstone.diasmart.database;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import edu.uwm.capstone.diasmart.types.EntryContext;
import edu.uwm.capstone.diasmart.types.EntryType;
import edu.uwm.capstone.diasmart.types.EntryTypeFactory;
import edu.uwm.capstone.diasmart.types.GlucoseType;
import edu.uwm.capstone.util.DateUtil;
import edu.uwm.capstone.util.DebugUtil;

/**Used to access the Entry objects from database
 * @author Palaniappan
 * @version 1.0
 */
public class EntryDAO {

	private DatabaseHelper dbh;

	/**
	 * @param databaseHelper
	 */
	public EntryDAO(DatabaseHelper databaseHelper) {
		this.dbh = databaseHelper;
	}

	/**
	 * @param context
	 * @param typeId
	 * @param entryDate
	 * @return
	 */
	private double getAverageForPeriod(EntryContext context, int typeId,
			Date entryDate) {
		double avgValue = 0.0D;
		Cursor result = this.dbh.getReadableDatabase().rawQuery(
				"SELECT avg(value) FROM entries WHERE type_id = " + typeId
						+ " AND " + "entry_time" + " > " + entryDate.getTime(),
				null);
		if (result != null)
			;
		try {
			if (result.moveToFirst()) {
				avgValue = getActualValue(context, typeId, result.getLong(0));

			}
			return avgValue;
		} finally {
			if (result != null)
				result.close();
		}
	}

	/**
	 * @param paramEntryContext
	 * @param paramInt
	 * @param paramLong
	 * @return
	 */
	public double getActualValue(EntryContext paramEntryContext, int paramInt,
			long paramLong) {
		return EntryTypeFactory.getInstance(paramInt).toActualValue(
				paramEntryContext, paramLong);
	}

	/**
	 * @param newContext
	 * @param newEntry
	 * @param newContent
	 */
	private void setContentValues(EntryContext newContext, Entry newEntry,
			ContentValues newContent) {
		newContent.put("entry_time", Long.valueOf(newEntry.getEntryDate()));
		newContent.put("type_id", Integer.valueOf(newEntry.getType()));
		if (newEntry.getCategoryId() < 0L)
			newEntry.setCategoryId(this.dbh.getCategoryDAO()
					.getDefaultCategory().getId());
		newContent.put("category_id", Long.valueOf(newEntry.getCategoryId()));
		if (newEntry.getSubTypeId() < 0L)
			newEntry.setSubTypeId(this.dbh.getSubTypeDAO().getDefaultSubType()
					.getId());
		newContent.put("sub_type_id", Long.valueOf(newEntry.getSubTypeId()));
		newContent.put("note", newEntry.getNote());
		newContent.put("value", Long.valueOf(getStoredValue(newContext,
				newEntry.getType(), newEntry.getValue())));
	}

	/**
	 * @param newContext
	 * @param type
	 * @param value
	 * @return
	 */
	public long getStoredValue(EntryContext newContext, int type, double value) {
		return EntryTypeFactory.getInstance(type).toStoredValue(newContext,
				value);
	}

	/**
	 * @param statsType
	 * @return
	 */
	public AverageStatistics getAverages(int statsType) {
		EntryContext newContext = new EntryContext(this.dbh.getContext());
		Calendar today = Calendar.getInstance();
		DateUtil.trimTime(today);
		double daily = getAverageForPeriod(newContext, statsType,
				today.getTime());
		today.add(6, -6);
		double weekly = getAverageForPeriod(newContext, statsType,
				today.getTime());
		Calendar currentTime = Calendar.getInstance();
		currentTime.setTime(today.getTime());
		currentTime.add(2, -1);
		double monthly = getAverageForPeriod(newContext, statsType,
				currentTime.getTime());
		currentTime.add(2, -2);
		return new AverageStatistics(statsType, daily, weekly, monthly,
				getAverageForPeriod(newContext, statsType,
						currentTime.getTime()));
	}

	/**
	 * @param paramString
	 * @return
	 */
	public ArrayList<Entry> getDisplayableEntries(String paramString) {
		EntryContext newContext = new EntryContext(this.dbh.getContext());
		Calendar today = Calendar.getInstance();
		ArrayList<Entry> entryArrayList = new ArrayList<Entry>();
		Cursor result = null;
		try {
			result = this.dbh.getReadableDatabase().rawQuery(paramString, null);
			long l1 = -2L;
			long l2 = -1L;
			while (result.move(1)) {
				Entry entry = new Entry(result.getLong(0), result.getLong(1),
						result.getInt(2), result.getLong(3), result.getLong(4),
						result.getString(6), getActualValue(newContext,
								result.getInt(2), result.getLong(5)));
				today.setTimeInMillis(entry.getEntryDate());
				DateUtil.trimTime(today);
				long l3 = today.getTimeInMillis() / 86400000L;
				if (l2 != l3) {
					Entry localEntry2 = new Entry(l1);
					localEntry2.setEntryDate(today.getTimeInMillis());
					entryArrayList.add(localEntry2);
					l1 -= 1L;
					l2 = l3;
				}
				entryArrayList.add(entry);
			}
			return entryArrayList;
		} catch (Exception localException) {
			DebugUtil.debug(this, "Unexpected error loading entries",
					localException);
			return entryArrayList;
		} finally {
			if (result != null)
				result.close();
		}
	}

	/**
	 * @param id
	 * @return
	 */
	public Entry getEntry(long id) {
		Cursor result = this.dbh
				.getReadableDatabase()
				.rawQuery(
						"SELECT _id,entry_time,type_id,category_id,sub_type_id,note,value from entries where _id="
								+ id, null);
		try {
			if (result.move(1)) {
				Entry localEntry = new Entry(result.getInt(0),
						result.getLong(1), result.getInt(2), result.getLong(3),
						result.getLong(4), result.getString(5), getActualValue(
								new EntryContext(this.dbh.getContext()),
								result.getInt(2), result.getLong(6)));
				localEntry.setModified(false);
				return localEntry;
			}
		} catch (Exception localException) {
			DebugUtil.debug(this, "Unexpected error loading entries",
					localException);

		} finally {
			if (result != null)
				result.close();
		}
		if (result != null)
			result.close();
		return null;
	}

	/**
	 * @param minEntryTime
	 * @param maxEntryTime
	 * @return
	 */
	public ArrayList<EntryType> getEntryTypesUsed(long minEntryTime,
			long maxEntryTime) {
		ArrayList<EntryType> entryTypeList = new ArrayList<EntryType>();
		StringBuilder queryBuilder = new StringBuilder();
		queryBuilder.append("SELECT DISTINCT ");
		queryBuilder.append("type_id");
		queryBuilder.append(" FROM entries");
		queryBuilder.append(" WHERE entry_time>=" + minEntryTime);
		queryBuilder.append(" AND entry_time<" + maxEntryTime);
		Cursor result = this.dbh.getReadableDatabase().rawQuery(
				queryBuilder.toString(), null);
		try {
			while (result.move(1)) {

				entryTypeList
						.add(EntryTypeFactory.getInstance(result.getInt(0)));
			}
			return entryTypeList;
		} finally {
			result.close();
		}
	}

	/**
	 * @param typeId
	 * @param paramDouble1
	 * @param paramDouble2
	 * @param entryTime
	 * @param paramBoolean
	 * @return
	 */
	public long getEntryCountInRange(int typeId, double paramDouble1,
			double paramDouble2, long entryTime, boolean paramBoolean) {
		EntryContext context = new EntryContext(this.dbh.getContext());
		GlucoseType localGlucoseType = (GlucoseType) EntryTypeFactory
				.getInstance(typeId);
		StringBuilder queryBuilder = new StringBuilder();
		queryBuilder.append("SELECT count(_id) ");
		queryBuilder.append("FROM entries ");
		queryBuilder.append("WHERE type_id=" + typeId + " ");
		queryBuilder.append("AND entry_time>=" + entryTime + " ");
		queryBuilder.append("AND value>");
		String str1;
		if (paramBoolean)
			str1 = "=";
		else
			str1 = "";
		queryBuilder.append(str1
				+ localGlucoseType.toStoredValue(context, paramDouble1) + " ");
		queryBuilder.append("AND value<");
		String str2;
		Cursor result;
		if (paramBoolean)
			str2 = "=";
		else
			str2 = "";
		queryBuilder.append(str2
				+ localGlucoseType.toStoredValue(context, paramDouble2) + " ");

		result = this.dbh.getReadableDatabase().rawQuery(
				queryBuilder.toString(), null);
		long l = 0L;
		try {
			if (result.move(1)) {
				l = result.getLong(0);
			}
			return l;
		} finally {
			result.close();
		}

	}

	/**
	 * @param minentryTime
	 * @param maxentryTime
	 * @return
	 */
	public Cursor getExportCursor(long minentryTime, long maxentryTime) {
		StringBuilder queryBuilder = new StringBuilder();
		queryBuilder.append("SELECT ");
		queryBuilder.append("e._id,");
		queryBuilder.append("e.entry_time,");
		queryBuilder.append("e.type_id,");
		queryBuilder.append("s.sub_type,");
		queryBuilder.append("c.category,");
		queryBuilder.append("e.value,");
		queryBuilder.append("e.note");
		queryBuilder.append(" FROM entries e,categories c,sub_types s ");
		queryBuilder.append(" WHERE ");
		queryBuilder.append(" e.category_id=c._id");
		queryBuilder.append(" AND e.sub_type_id=s._id");
		queryBuilder.append(" AND e.entry_time>=" + minentryTime);
		queryBuilder.append(" AND e.entry_time<" + maxentryTime);
		queryBuilder.append(" ORDER BY e.entry_time DESC, e._id");
		return this.dbh.getReadableDatabase().rawQuery(queryBuilder.toString(),
				null);
	}

	/**
	 * @return
	 */
	public boolean isEmpty() {
		Cursor result = this.dbh.getReadableDatabase().rawQuery(
				"select (select _id from entries limit 1) is not null;", null);
		try {
			if (result.move(1)) {
				boolean value = "0".equals(result.getString(0));
				return value;
			}
			return true;
		} catch (Exception localException) {
			DebugUtil.debug(this, "Unexpected error loading entries",
					localException);
			return false;
		} finally {
			if (result != null)
				result.close();
		}
	}

	/**
	 * @param entry
	 * @return
	 */
	public boolean save(Entry entry) {
		EntryContext context = new EntryContext(this.dbh.getContext());
		if (!entry.isModified())
			return false;
		if (entry.getId() > 0L)
			insert(this.dbh.getWritableDatabase(), context, entry);
		else {
			update(this.dbh.getWritableDatabase(), context, entry);

		}
		return true;
	}

	/**
	 * @param db
	 * @param context
	 * @param entry
	 */
	void insert(SQLiteDatabase db, EntryContext context, Entry entry) {
		ContentValues newContent = new ContentValues();
		setContentValues(context, entry, newContent);
		long id = db.insert("entries", null, newContent);
		entry.setId(id);
		entry.setModified(false);
		DatabaseHelper.fireTableRowChanged("entries", id);
	}

	/**
	 * @param id
	 */
	public void delete(long id) {
		this.dbh.getWritableDatabase().delete("entries", "_id=" + id, null);
		DatabaseHelper.fireTableRowChanged("entries", id);
	}

	/**
	 * @param entry
	 */
	public void delete(Entry entry) {
		delete(entry.getId());
	}

	/**
	 * @param entryTime
	 */
	public void deleteRecords(long entryTime) {
		this.dbh.getWritableDatabase().execSQL(
				"delete from entries where entry_time<" + entryTime);
	}

	/**
	 * @param db
	 * @param context
	 * @param entry
	 */
	void update(SQLiteDatabase db, EntryContext context, Entry entry) {
		ContentValues newContent = new ContentValues();
		setContentValues(context, entry, newContent);
		db.update("entries", newContent, "_id=" + entry.getId(), null);
		entry.setModified(false);
		DatabaseHelper.fireTableRowChanged("entries", entry.getId());
	}

	/**
	 * @param newCategoryId
	 * @param oldCategoryId
	 */
	public void updateCategory(long newCategoryId, long oldCategoryId) {
		this.dbh.getWritableDatabase().execSQL(
				"UPDATE entries SET category_id=" + oldCategoryId + " WHERE "
						+ "category_id" + "=" + newCategoryId);
	}

	/**
	 * @param newTypeId
	 * @param oldTypeId
	 */
	public void updateSubType(long newTypeId, long oldTypeId) {
		this.dbh.getWritableDatabase().execSQL(
				"UPDATE entries SET sub_type_id=" + oldTypeId + " WHERE "
						+ "sub_type_id" + "=" + newTypeId);
	}

}
